import pyodbc
import logging
from config.config import driver, server, database, username, password, BASE_DIR
from util.initial_log import initial_log_config


class DBUtil:
    conn = None
    cursor = None

    @classmethod
    def get_conn(cls):
        cls.conn = pyodbc.connect(
            f'DRIVER={driver};'
            f'SERVER={server};'
            f'DATABASE={database};'
            f'UID={username};'
            f'PWD={password}'
        )
        return cls.conn

    @classmethod
    def close_conn(cls):
        if cls.conn is not None:
            cls.conn.close()
            cls.conn = None

    @classmethod
    def select_one(cls, sql, param=None):
        cursor = None
        res = None
        try:
            cls.conn = cls.get_conn()
            cursor = cls.conn.cursor()
            if param is not None:
                cursor.execute(sql, param)
            else:
                cursor.execute(sql)
            res = cursor.fetchone()
        except Exception as err:
            logging.error("select sql throw error:", str(err))
        finally:
            if cursor is not None:
                cursor.close()
            cls.close_conn()
            return res

    @classmethod
    def select_all(cls, sql, param=None):
        cursor = None
        res = None
        try:
            cls.conn = cls.get_conn()
            cursor = cls.conn.cursor()
            if param is not None:
                cursor.execute(sql, param)
            else:
                cursor.execute(sql)
            res = cursor.fetchall()
        except Exception as err:
            logging.error("select sql throw error:", str(err), f"sql: {sql}")
        finally:
            if cursor is not None:
                cursor.close()
            cls.close_conn()
            return res

    @classmethod
    def uid_db(cls, sql, param=None, quiet=False):
        cursor = None
        try:
            cls.conn = cls.get_conn()
            cursor = cls.conn.cursor()
            if param is not None:
                cursor.execute(sql, param)
            else:
                cursor.execute(sql)
            if not quiet:
                logging.info(f"affect rows：{cursor.rowcount}")
            cls.conn.commit()
        except Exception as err:
            cls.conn.rollback()
            logging.error("Uid sql execute fail：", str(err))
        finally:
            if cursor is not None:
                cursor.close()
            cls.close_conn()
